package com.realshare.frame.component.quartz.service;

import com.realshare.frame.component.quartz.model.SysDbConfig;
import com.realshare.frame.component.quartz.model.SysDbQuartz;
import com.realshare.frame.component.quartz.model.SysDbQuartzLog;
import com.realshare.frame.util.DateUtils;
import com.realshare.frame.util.EasyHashMap;
import com.realshare.frame.util.StringUtils;
import com.zaxxer.hikari.HikariDataSource;
import org.beetl.sql.core.ConnectionSource;
import org.beetl.sql.core.ConnectionSourceHelper;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.db.*;
import org.quartz.DisallowConcurrentExecution;
import org.quartz.JobDataMap;
import org.quartz.JobDetail;
import org.quartz.JobExecutionContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.scheduling.quartz.QuartzJobBean;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.UUID;

/**
 * 数据库数据处理任务
 * 使用@DisallowConcurrentExecution设置不并发执行
 * author leisure
 * email 15075107120@139.com
 * create 2018-12-14 下午 4:33
 * modify 2018-12-14 下午 4:33
 **/
@Service
@DisallowConcurrentExecution
public class DBQuartzScheduler extends QuartzJobBean implements Serializable {

    private Logger log = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private SQLManager sqlManager;
    @Autowired
    private QuartzManager quartzManager;
    @Autowired
    private Environment environment;

    public void init() {
        String frequency = environment.getProperty("quartz.config.listen.frequency");
        if (StringUtils.isNotBlank(frequency)) {
            log.info("开始初始化数据库数据处理定时任务");
            List<SysDbQuartz> quartzList = sqlManager.query(SysDbQuartz.class).andEq("status", "1").select();
            for (SysDbQuartz sysQuartz : quartzList) {
                final String name = sysQuartz.getName();
                final String group = "SysDbQuartz";
                JobDataMap jobDataMap = new JobDataMap();
                jobDataMap.put("sysQuartzId", sysQuartz.getId());
                quartzManager.scheduleJob(name, group, name, sysQuartz.getCron(), DBQuartzScheduler.class, jobDataMap);
                Runnable runnable = new Runnable() {
                    @Override
                    public void run() {
                        try {
                            Thread.sleep(10000);
                            log.info("服务启动后立即执行一次数据库数据定时任务【" + name + "】");
                            quartzManager.triggerJob(name, group);
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                    }
                };
                new Thread(runnable).start();
                sysQuartz.setTaskStatus(1);
                sqlManager.updateById(sysQuartz);
            }
            log.info("结束初始化数据库数据处理定时任务");
            log.info("启动数据库数据处理定时任务动态变更监听，每【" + frequency + "】扫描一次");
            String name = "定时任务动态变更监听";
            quartzManager.scheduleJob(name, "listen", name, frequency, ListenQuartzScheduler.class, new JobDataMap());
        }
    }

    @Override
    protected void executeInternal(JobExecutionContext context) {
        long begin = System.currentTimeMillis();
        JobDetail jobDetail = context.getJobDetail();
        String workName = jobDetail.getKey().toString();
        log.info("开始执行处理数据库数据定时任务，任务名称为：" + workName);
        SysDbQuartz sysQuartz = sqlManager.query(SysDbQuartz.class).andEq("status", "2").single();
        SysDbQuartzLog quartzLog = new SysDbQuartzLog();
        StringBuilder msg = new StringBuilder();
        if (sysQuartz != null) {
            String msgStr = "发现异常任务，导致数据库数据处理任务暂停，任务信息为：“" + sysQuartz.toString() + "”，请及时处理！";
            log.info(msgStr);
            msg.append("；").append(msgStr);
            log.error(msgStr);
            quartzLog.setDescription(msgStr);
            quartzLog.setStatus(0);
        } else {
            String sysQuartzId = (String) jobDetail.getJobDataMap().get("sysQuartzId");
            sysQuartz = sqlManager.single(SysDbQuartz.class, sysQuartzId);
            int taskRecordCount = 0, outSqlCount = 0, saveSuccessCount = 0;
            try {
                if (StringUtils.isNotBlank(sysQuartz.getTaskSql())) {
                    String[] taskSqls = sysQuartz.getTaskSql().split(";");
                    SQLManager taskSqlManager = sqlManager;
                    if (StringUtils.isNotBlank(sysQuartz.getTaskDbId())) {
                        //如果指定了任务数据库连接
                        SysDbConfig sysDbConfig = sqlManager.single(SysDbConfig.class, sysQuartz.getTaskDbId());
                        DataSource dataSource = getDataSourceByConfig(sysDbConfig);
                        DBStyle dbStyle = getDBStyleByUrl(sysDbConfig.getUrl());
                        ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
                        taskSqlManager = new SQLManager(dbStyle, source);
                    }
                    for (String taskSql : taskSqls) {
                        String msgStr = "任务sql为：" + taskSql;
                        log.info(msgStr);
                        msg.append("；").append(msgStr);
                        taskRecordCount += taskSqlManager.executeUpdate(new SQLReady(taskSql));
                    }
                    String msgStr = "成功结束执行处理数据库数据定时任务，共处理【" + taskSqls.length + "】条sql，【" + taskRecordCount + "】条记录，任务名称为：" + workName;
                    log.info(msgStr);
                    msg.append("；").append(msgStr);
                }
                if (StringUtils.isNotBlank(sysQuartz.getDataOutSql())
                        && StringUtils.isNotBlank(sysQuartz.getDataTargetInitSql())
                        && StringUtils.isNotBlank(sysQuartz.getDataTargetSaveSql())) {
                    SQLManager dataSourceSqlManager = sqlManager;
                    SQLManager dataTargetSqlManager = sqlManager;
                    if (StringUtils.isNotBlank(sysQuartz.getDataSourceDbId())) {
                        //如果指定了源数据库连接
                        SysDbConfig sysDbConfig = sqlManager.single(SysDbConfig.class, sysQuartz.getDataSourceDbId());
                        DataSource dataSource = getDataSourceByConfig(sysDbConfig);
                        ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
                        // DBStyle dbStyle = getDBStyleByUrl(sysDbConfig.getUrl());
                        // dataSourceSqlManager = new SQLManager(dbStyle, source);
                        dataSourceSqlManager = SQLManager.newBuilder(source).build();
                    }
                    if (StringUtils.isNotBlank(sysQuartz.getDataTargetDbId())) {
                        //如果指定了目标数据库连接
                        SysDbConfig sysDbConfig = sqlManager.single(SysDbConfig.class, sysQuartz.getDataTargetDbId());
                        DataSource dataSource = getDataSourceByConfig(sysDbConfig);
                        ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
                        // DBStyle dbStyle = getDBStyleByUrl(sysDbConfig.getUrl());
                        // dataTargetSqlManager = new SQLManager(dbStyle, source);
                        dataTargetSqlManager = SQLManager.newBuilder(source).build();
                    }
                    String[] initSqlArray = sysQuartz.getDataTargetInitSql().split(";");
                    try {
                        String msgStr = "目标库初始化sql为：" + sysQuartz.getDataTargetInitSql();
                        log.info(msgStr);
                        msg.append("；").append(msgStr);
                        for (String initSql : initSqlArray) {
                            dataTargetSqlManager.executeUpdate(new SQLReady(initSql));
                        }
                    } catch (Exception e) {
                        String msgStr = "执行初始化（建表，索引等）sql出错，可能为重复执行，已忽略，建议sql中自行作是否重复执行判断，错误信息为：" + e.getMessage();
                        log.info(msgStr);
                        msg.append("；").append(msgStr);
                    }
                    String outSql = sysQuartz.getDataOutSql().replaceAll(";", "");
                    long defaultTime = 19700101000000L;
                    if (sysQuartz.getLastRunTime() != null) {
                        defaultTime = sysQuartz.getLastRunTime();
                    }
                    outSql = outSql.replaceAll("#last_run_time#", defaultTime + "");
                    //dataSourceSqlManager.setNc(new DefaultNameConversion());
                    List<EasyHashMap> mapList = dataSourceSqlManager.execute(new SQLReady(outSql), EasyHashMap.class);
                    outSqlCount = mapList.size();
                    //saveSql类似于insert into table(id,name) values(#id#,#name#)
                    String[] saveReadySql = sysQuartz.getDataTargetSaveSql().replaceAll(";", "").split("#");
                    PreparedStatement statement = null;
                    for (int i = 0; i < mapList.size(); i++) {
                        EasyHashMap map = mapList.get(i);
                        if (statement == null) {
                            StringBuilder saveSql = new StringBuilder();
                            for (String key : saveReadySql) {
                                if (map.containsKey(key)) {
                                    saveSql.append("?");
                                } else {
                                    saveSql.append(key);
                                }
                            }
                            statement = dataTargetSqlManager.getDs().getMaster().prepareStatement(saveSql.toString());
                            String msgStr = "目标库数据保存sql为：" + saveSql.toString();
                            log.info(msgStr);
                            msg.append("；").append(msgStr);
                        }
                        int index = 1;
                        for (String key : saveReadySql) {
                            if (map.containsKey(key)) {
                                statement.setString(index, map.getString(key));
                                index++;
                            }
                        }
                        if (index > 1) {
                            statement.addBatch();
                        }
                        if (i % 10000 == 0) {
                            saveSuccessCount += statement.executeBatch().length;
                            statement.clearBatch();
                        }
                    }
                    if (statement != null) {
                        saveSuccessCount += statement.executeBatch().length;
                        statement.clearBatch();
                    }
                    String msgStr = "成功保存目标库数据，共保存【" + saveSuccessCount + "】条记录，任务名称为：" + workName;
                    log.info(msgStr);
                    msg.append("；").append(msgStr);
                }
                quartzLog.setDescription(msg.toString());
                quartzLog.setStatus(1);
                if (saveSuccessCount > 0) {
                    SQLReady sqlReady = new SQLReady("update sys_db_quartz set last_run_time = ?", DateUtils.getNow(DateUtils.PATTERN_DEFAULT));
                    sqlManager.executeUpdate(sqlReady);
                }
            } catch (Exception e) {
                e.printStackTrace();
                String msgStr = "执行处理数据库数据定时任务时出错，任务名称为：" + workName + "，错误信息为：" + e.getMessage();
                quartzLog.setDescription(msgStr);
                quartzLog.setStatus(0);
                log.info(msgStr);
            }
            quartzLog.setTaskRecordCount((long) taskRecordCount);
            quartzLog.setOutSqlCount((long) outSqlCount);
            quartzLog.setSaveSuccessCount((long) saveSuccessCount);
        }
        quartzLog.setCreateTime(DateUtils.getNowDateTime());
        quartzLog.setQuartzId(sysQuartz.getId());
        quartzLog.setId(UUID.randomUUID().toString());
        long end = System.currentTimeMillis();
        quartzLog.setTimeLimit(end - begin);
        sqlManager.insert(quartzLog);
    }

    private DBStyle getDBStyleByUrl(String url) {
        DBStyle dbStyle = new MySqlStyle();
        if (url.toLowerCase().contains("oracle")) {
            dbStyle = new OracleStyle();
        } else if (url.toLowerCase().contains("db2")) {
            dbStyle = new DB2SqlStyle();
        } else if (url.toLowerCase().contains("sqlserver")) {
            dbStyle = new SqlServer2012Style();
        } else if (url.toLowerCase().contains("sqlite")) {
            dbStyle = new SQLiteStyle();
        } else if (url.toLowerCase().contains("postgresql")) {
            dbStyle = new PostgresStyle();
        }
        return dbStyle;
    }

    private DataSource getDataSourceByConfig(SysDbConfig config) {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(config.getUrl());
        ds.setUsername(config.getUserName());
        ds.setPassword(config.getPassword());
        ds.setDriverClassName(config.getDriverClassName());
        return ds;
    }

}
